package com.ht.modules.system.online.config.util;

import freemarker.template.TemplateException;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.model.relational.Namespace;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.mapping.Table;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.hibernate.tool.schema.TargetType;
import com.ht.utils.oConvertUtils;
import com.ht.modules.system.online.cgform.entity.OnlCgformField;
import com.ht.modules.system.online.config.exception.DBException;
import com.ht.modules.system.online.config.model.CgformConfigModel;
import com.ht.modules.system.online.config.model.DataBaseConfig;
import com.ht.modules.system.online.config.service.DbTableHandleI;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class DbTableProcess {
  private static final Logger log = LoggerFactory.getLogger(DbTableProcess.class);

  private static final String tpl_url = "com/ht/modules/system/online/config/engine/tableTemplate.ftl";

  private static DbTableHandleI dbTableHandle;

  public DbTableProcess() throws SQLException, DBException {
    dbTableHandle = DbTableUtil.getTableHandle();
  }

  public static void createTable(CgformConfigModel model) throws IOException, TemplateException, HibernateException, SQLException, DBException {
    String dbType = DbTableUtil.getDatabaseType();
    String xml = FreemarkerHelper.parseTemplate("com/ht/modules/system/online/config/engine/tableTemplate.ftl", getRootMap(model, dbType));
    log.info(xml);
    Map<String, Object> settings = new HashMap<>();
    DataBaseConfig dbconfig = model.getDbConfig();
    settings.put("hibernate.connection.driver_class", dbconfig.getDriverClassName());
    settings.put("hibernate.connection.url", dbconfig.getUrl());
    settings.put("hibernate.connection.username", dbconfig.getUsername());
    settings.put("hibernate.connection.password", dbconfig.getPassword());
    settings.put("hibernate.show_sql", Boolean.TRUE);
    settings.put("hibernate.format_sql", Boolean.TRUE);
    settings.put("hibernate.dialect", DbTableUtil.getDialect(dbType));
    settings.put("hibernate.hbm2ddl.auto", "create");
    settings.put("hibernate.connection.autocommit", Boolean.FALSE);
    settings.put("hibernate.current_session_context_class", "thread");
    StandardServiceRegistry standardServiceRegistry = (new StandardServiceRegistryBuilder()).applySettings(settings).build();
    MetadataSources ms = new MetadataSources(standardServiceRegistry);
    InputStream xmlInputStream = new ByteArrayInputStream(xml.getBytes());
    ms.addInputStream(xmlInputStream);
    Metadata metadata = ms.buildMetadata();

    //将表说明添加到表备注中
    Iterable<Namespace> namespaces = metadata.getDatabase().getNamespaces();
    for (Namespace namespace : namespaces) {
      for ( Table table : namespace.getTables() ) {
        table.setComment(model.getContent());
      }
    }
    SchemaExport dbExport = new SchemaExport();
    dbExport.create(EnumSet.of(TargetType.DATABASE), metadata);
    xmlInputStream.close();
    List<Exception> exceptionList = dbExport.getExceptions();
    for (Exception exception : exceptionList) {
      if ("java.sql.SQLSyntaxErrorException".equals(exception.getCause().getClass().getName())) {
        SQLSyntaxErrorException e = (SQLSyntaxErrorException)exception.getCause();
        if ("42000".equals(e.getSQLState()))
          continue;
      } else if ("com.microsoft.sqlserver.jdbc.SQLServerException".equals(exception.getCause().getClass().getName())) {
        continue;
      }
      throw new DBException(exception.getMessage());
    }
  }

  public List<String> updateTable(CgformConfigModel model) throws DBException, SQLException {
    String dataType = DbTableUtil.getDatabaseType();
    String tableName = DbTableUtil.getTableName(model.getTableName(), dataType);
    String alterSql = "alter table  " + tableName + " ";
    List<String> alterList = new ArrayList<>();
    try {
      Map<String, ColumnMeta> dbMetaCol = getDbMetaColumns(null, tableName);
      Map<String, ColumnMeta> configCol = getConfigColumns(model);
      Map<String, String> newAndOldFieldMap = getNewAndOldFieldName(model.getColumns());
      for (String columnName : configCol.keySet()) {
        if (!dbMetaCol.containsKey(columnName)) {
          ColumnMeta columnMeta = configCol.get(columnName);
          String oldFieldName = newAndOldFieldMap.get(columnName);
          if (newAndOldFieldMap.containsKey(columnName) && dbMetaCol.containsKey(oldFieldName)) {
            ColumnMeta columnMeta1 = dbMetaCol.get(oldFieldName);
            String changeSql = dbTableHandle.getReNameFieldName(columnMeta);
            if ("SQLSERVER".equals(dataType)) {
              alterList.add(changeSql);
            } else {
              alterList.add(alterSql + changeSql);
            }
            String oldFieldSql = getUpdateOldFieldSql(columnName, columnMeta.getColumnId());
            alterList.add(oldFieldSql);
            if (!columnMeta1.equals(columnMeta)) {
              alterList.add(alterSql + getUpdateColumnSql(columnMeta, columnMeta1));
              if ("POSTGRESQL".equals(dataType))
                alterList.add(alterSql + getUpdateSpecialSql(columnMeta, columnMeta1));
            }
            if (!"SQLSERVER".equals(dataType) && !columnMeta1.equalsComment(columnMeta))
              alterList.add(getCommentSql(columnMeta));
            continue;
          }
          alterList.add(alterSql + getAddColumnSql(columnMeta));
          if (!"SQLSERVER".equals(dataType) && StringUtils.isNotEmpty(columnMeta.getComment()))
            alterList.add(getCommentSql(columnMeta));
          continue;
        }
        ColumnMeta dataColumnMeta = dbMetaCol.get(columnName);
        ColumnMeta cgFormColumnMeta = configCol.get(columnName);
        if (!dataColumnMeta.equalsByDataType(cgFormColumnMeta, dataType))
          alterList.add(alterSql + getUpdateColumnSql(cgFormColumnMeta, dataColumnMeta));
        if (!"SQLSERVER".equals(dataType) && !dataColumnMeta.equalsComment(cgFormColumnMeta))
          alterList.add(getCommentSql(cgFormColumnMeta));
      }
      for (String columnName : dbMetaCol.keySet()) {
        if (!configCol.containsKey(columnName.toLowerCase()) && !newAndOldFieldMap.containsValue(columnName.toLowerCase()))
          alterList.add(alterSql + getDropColumnSql(columnName));
      }
    } catch (SQLException e1) {
      throw new RuntimeException();
    }
    log.info(alterList.toString());
    return alterList;
  }

  private static Map<String, Object> getRootMap(CgformConfigModel model, String dataType) {
    Map<String, Object> map = new HashMap<>();
    for (OnlCgformField field : model.getColumns())
      field.setDbDefaultVal(judgeIsNumber(field.getDbDefaultVal()));
    map.put("entity", model);
    map.put("dataType", dataType);
    return map;
  }

  private Map<String, ColumnMeta> getDbMetaColumns(String schemaName, String tableName) throws SQLException {
    Map<String, ColumnMeta> columnMap = new HashMap<>();
    Connection conn = null;
    try {
      conn = DbTableUtil.getConnection();
    } catch (Exception e) {
      log.error(e.getMessage(), e);
    }
    DatabaseMetaData dbMetaData = conn.getMetaData();
    ResultSet rs = dbMetaData.getColumns(null, schemaName, tableName, "%");
    while (rs.next()) {
      ColumnMeta columnMeta = new ColumnMeta();
      columnMeta.setTableName(tableName);
      String columnName = rs.getString("COLUMN_NAME").toLowerCase();
      columnMeta.setColumnName(columnName);
      String typeName = rs.getString("TYPE_NAME");
      int decimalDigits = rs.getInt("DECIMAL_DIGITS");
      String colunmType = dbTableHandle.getMatchClassTypeByDataType(typeName, decimalDigits);
      columnMeta.setColunmType(colunmType);
      int columnSize = rs.getInt("COLUMN_SIZE");
      columnMeta.setColumnSize(columnSize);
      columnMeta.setDecimalDigits(decimalDigits);
      String isNullable = (rs.getInt("NULLABLE") == 1) ? "Y" : "N";
      columnMeta.setIsNullable(isNullable);
      String comment = rs.getString("REMARKS");
      columnMeta.setComment(comment);
      String columnDef = rs.getString("COLUMN_DEF");
      String fieldDefault = (judgeIsNumber(columnDef) == null) ? "" : judgeIsNumber(columnDef);
      columnMeta.setFieldDefault(fieldDefault);
      log.info("getColumnMetadataFormDataBase --->COLUMN_NAME:" + columnName.toUpperCase() + " TYPE_NAME :" + typeName + " DECIMAL_DIGITS:" + decimalDigits + " COLUMN_SIZE:" + columnSize);
      columnMap.put(columnName, columnMeta);
    }
    return columnMap;
  }

  private Map<String, ColumnMeta> getConfigColumns(CgformConfigModel model) {
    Map<String, ColumnMeta> columnMap = new HashMap<>();
    List<OnlCgformField> fieldList = model.getColumns();
    for (OnlCgformField field : fieldList) {
      ColumnMeta columnMeta = new ColumnMeta();
      columnMeta.setTableName(model.getTableName().toLowerCase());
      columnMeta.setColumnId(field.getId());
      columnMeta.setColumnName(field.getDbFieldName().toLowerCase());
      columnMeta.setColumnSize(field.getDbLength().intValue());
      columnMeta.setColunmType(field.getDbType());
      columnMeta.setIsNullable((field.getDbIsNull().intValue() == 1) ? "Y" : "N");
      columnMeta.setComment(field.getDbFieldTxt());
      columnMeta.setDecimalDigits(field.getDbPointLength().intValue());
      columnMeta.setFieldDefault(judgeIsNumber(field.getDbDefaultVal()));
      columnMeta.setPkType((model.getJformPkType() == null) ? "UUID" : model.getJformPkType());
      columnMeta.setOldColumnName((field.getDbFieldNameOld() != null) ? field.getDbFieldNameOld().toLowerCase() : null);
      log.info("getColumnMetadataFormCgForm ---->COLUMN_NAME:" + field.getDbFieldName().toLowerCase() + " TYPE_NAME:" + field.getDbType().toLowerCase() + " DECIMAL_DIGITS:" + field
          .getDbPointLength() + " COLUMN_SIZE:" + field.getDbLength());
      columnMap.put(field.getDbFieldName().toLowerCase(), columnMeta);
    }
    return columnMap;
  }

  private Map<String, String> getNewAndOldFieldName(List<OnlCgformField> fieldList) {
    Map<String, String> map = new HashMap<>();
    for (OnlCgformField field : fieldList)
      map.put(field.getDbFieldName(), field.getDbFieldNameOld());
    return map;
  }

  private String getDropColumnSql(String fieldName) {
    return dbTableHandle.getDropColumnSql(fieldName);
  }

  private String getUpdateColumnSql(ColumnMeta cgformcolumnMeta, ColumnMeta datacolumnMeta) throws DBException {
    return dbTableHandle.getUpdateColumnSql(cgformcolumnMeta, datacolumnMeta);
  }

  private String getUpdateSpecialSql(ColumnMeta cgformcolumnMeta, ColumnMeta datacolumnMeta) {
    return dbTableHandle.getSpecialHandle(cgformcolumnMeta, datacolumnMeta);
  }

  private String getReNameFieldName(ColumnMeta columnMeta) {
    return dbTableHandle.getReNameFieldName(columnMeta);
  }

  private String getAddColumnSql(ColumnMeta columnMeta) {
    return dbTableHandle.getAddColumnSql(columnMeta);
  }

  private String getCommentSql(ColumnMeta columnMeta) {
    return dbTableHandle.getCommentSql(columnMeta);
  }

  private String getUpdateOldFieldSql(String columnName, String id) {
    return "update cgform_field set old_field_name= '" + columnName + "' where id='" + id + "'";
  }

  private int updateFieldName(String columnName, String id, Session session) {
    return session.createSQLQuery("update cgform_field set old_field_name= '" + columnName + "' where id='" + id + "'").executeUpdate();
  }

  private static String judgeIsNumber(String text) {
    if (StringUtils.isNotEmpty(text))
      try {
        Double.valueOf(text);
      } catch (Exception e) {
        if (!text.startsWith("'") || !text.endsWith("'"))
          text = "'" + text + "'";
      }
    return text;
  }

  public String dropIndex(String indexName, String tableName) {
    return dbTableHandle.dropIndexs(indexName, tableName);
  }

  public static List<String> getIndexInfo(String tbname) throws SQLException {
    Connection conn = null;
    ResultSet rs = null;
    List<String> list = new ArrayList<>();
    try {
      conn = DbTableUtil.getConnection();
      DatabaseMetaData dbmd = conn.getMetaData();
      rs = dbmd.getIndexInfo(null, null, tbname, false, false);
      ResultSetMetaData md = rs.getMetaData();
      while (rs.next()) {
        String index_name = rs.getString("INDEX_NAME");
        if (oConvertUtils.isEmpty(index_name))
          index_name = rs.getString("index_name");
        if (oConvertUtils.isNotEmpty(index_name))
          list.add(index_name);
      }
    } catch (SQLException e) {
      log.error(e.getMessage(), e);
    } finally {
      if (conn != null)
        conn.close();
    }
    return list;
  }
}
